﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DRegionCategory
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into RegionCategory(RegionCategoryId,FamousRegionId,Name,CategoryType,DateCreated) values (@RegionCategoryId,@FamousRegionId,@Name,@CategoryType,@DateCreated)";

        private const string SQL_UPDATE = "update RegionCategory set FamousRegionId=@FamousRegionId,Name=@Name,CategoryType=@CategoryType,DateCreated=@DateCreated where RegionCategoryId=@RegionCategoryId ";

        private const string SQL_DELETE = "delete from RegionCategory where FamousRegionId=@FamousRegionId ";

        private const string SQL_DELETE_ID = "delete from RegionCategory where RegionCategoryId=@RegionCategoryId ";

        private const string SQL_SELECT_FAMOUSREGIONID = "select RegionCategoryId,FamousRegionId,Name,CategoryType,DateCreated from RegionCategory where FamousRegionId=@FamousRegionId";

        private const string SQL_SELECT_ID = "select RegionCategoryId,FamousRegionId,Name,CategoryType,DateCreated from RegionCategory where RegionCategoryId=@RegionCategoryId";

        private const string SQL_SELECT_REGION_CATEGORY_BY_TYPE = "select r.RegionCategoryId,r.FamousRegionId,r.Name,r.CategoryType,r.DateCreated from RegionCategory r inner join FamousRegion f On r.FamousRegionId=f.FamousRegionId where r.CategoryType=@CategoryType and f.bak1 is null";
                
        #endregion

        #region 公共接口

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="regionCategoryInfo"></param>
        /// <returns></returns>
        public bool Insert(RegionCategoryInfo regionCategoryInfo)
        {
            if (regionCategoryInfo == null)
                throw new ArgumentNullException("regionCategoryInfo");

            SqlParameter[] parms = GetInsertParms(regionCategoryInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="regionCategoryInfo"></param>
        /// <returns></returns>
        public bool Update(RegionCategoryInfo regionCategoryInfo)
        {
            if (regionCategoryInfo == null || regionCategoryInfo.RegionCategoryId.ToString().Length != 18)
                throw new ArgumentNullException("regionCategoryInfo");

            SqlParameter[] parms = GetInsertParms(regionCategoryInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 根据regionCategoryId删除
        /// </summary>
        /// <param name="regionCategoryId"></param>
        /// <returns></returns>
        public int DeleteByRegionCategoryId(long regionCategoryId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@RegionCategoryId",SqlDbType.BigInt)
                };
            parms[0].Value = regionCategoryId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE_ID, parms);

        }

        /// <summary>
        /// 根据FamousRegionId删除
        /// </summary>
        /// <param name="famousRegionId"></param>
        /// <returns></returns>
        public int DeleteByFamousRegionId(long famousRegionId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionId",SqlDbType.BigInt)
                };
            parms[0].Value = famousRegionId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE, parms);

        }

        /// <summary>
        /// 根据famousRegionId获取所有名人专区模块
        /// </summary>
        /// <param name="famousRegionId"></param>
        /// <returns></returns>
        public List<RegionCategoryInfo> GetAllRegionCategoryByFamousRegionId(long famousRegionId)
        {
            List<RegionCategoryInfo> regionCategoryInfos = new List<RegionCategoryInfo>();

            RegionCategoryInfo regionCategoryInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@FamousRegionId",SqlDbType.BigInt)
                };
            parms[0].Value = famousRegionId;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_FAMOUSREGIONID, parms))
            {
                while (reader.Read())
                {
                    regionCategoryInfo = GetInstanceByDataReader(reader);
                    if (regionCategoryInfo != null && regionCategoryInfo.RegionCategoryId != 0)
                        regionCategoryInfos.Add(regionCategoryInfo);
                }
            }
            return regionCategoryInfos;
        }

        /// <summary>
        /// 根据文章Id获取文章
        /// </summary>
        /// <param name="paperId"></param>
        /// <returns></returns>
        public RegionCategoryInfo GetRegionCategoryInfo(long regionCategoryId)
        {
            RegionCategoryInfo regionCategoryInfo = new RegionCategoryInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@RegionCategoryId",SqlDbType.BigInt)
                };
            parms[0].Value = regionCategoryId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    regionCategoryInfo = GetInstanceByDataReader(reader);

                }
            }
            return regionCategoryInfo;
        }

        public List<RegionCategoryInfo> GetRegionCategoryListByType(int CategoryType)
        {
            List<RegionCategoryInfo> regionCategoryInfos = new List<RegionCategoryInfo>();

            RegionCategoryInfo regionCategoryInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@CategoryType",SqlDbType.Int)
                };
            parms[0].Value = CategoryType;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_REGION_CATEGORY_BY_TYPE, parms))
            {
                while (reader.Read())
                {
                    regionCategoryInfo = GetInstanceByDataReader(reader);
                    if (regionCategoryInfo != null && regionCategoryInfo.RegionCategoryId != 0)
                        regionCategoryInfos.Add(regionCategoryInfo);
                }
            }
            return regionCategoryInfos;
        }
        #endregion


        #region 私有接口

        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(RegionCategoryInfo model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@RegionCategoryId", SqlDbType.BigInt,8),
					new SqlParameter("@FamousRegionId", SqlDbType.BigInt,8),
					new SqlParameter("@Name", SqlDbType.NVarChar,20),
                    new SqlParameter("@CategoryType", SqlDbType.Int,4),
					new SqlParameter("@DateCreated", SqlDbType.DateTime)};
            parameters[0].Value = model.RegionCategoryId;
            parameters[1].Value = model.FamousRegionId;
            parameters[2].Value = model.Name;
            parameters[3].Value = model.CategoryType;
            parameters[4].Value = model.DateCreated;

            return SetInsertParmsCheck(parameters);
        }

        private RegionCategoryInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            //throw new NotImplementedException();
            RegionCategoryInfo current = new RegionCategoryInfo();
            //RegionCategoryId,FamousRegionId,Name,DateCreated
            current.RegionCategoryId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            current.FamousRegionId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            current.Name = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
            current.CategoryType = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);
            current.DateCreated = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);

            return current;
        }

        #endregion

    }
}
